<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021/10/23 0023
 * Time: 下午 4:17
 * author:白大大
 */

namespace app\controller;


use app\model\Users;
use think\Collection;
use think\DbManager;
use think\facade\Db;

class DataTest
{

    function index(){
        //return "datatest-index";

        //$db = new DbManager();
        //$db->getLastSql()
        ///$data = $db->table("users")->select();
        //$data = Db::connect("mysql")->table("users")->select();
        //$data = Db::table("users")->where("id",1)->find();
        //return Db::getLastSql();
        //$data = Db::table("users")->where("id",100)->findOrEmpty();
        //$data = Db::table("users")->where("id",100)->findOrFail();
        //$data = Db::table("users")->where("id",100)->selectOrFail();
        //$data = Db::table("users")->where("sex",0)->select();
        //$data = Db::table("users")->where("sex",0)->select()->toArray();
       // $data = Db::table("users")->where("sex",0)->select()->toJson();
        //$data = Db::table("users")->where("id",1)->value("name");
        //$data = Db::table("users")->where("sex",0)->column("name");
        //$data = Db::table("users")->column("name","id");

        //每次取3条
//        Db::table("users")->chunk(3,function ($users){
//
//            foreach ($users as $user){
//
//                //print_r($user);
//                dump($user);
//            }
//            echo "1";
//        });
//        $cursor = Db::table("users")->cursor();
//        foreach ($cursor as $cur){
//            dump($cur);
//        }
        //dump($data);
        //return json($data);

       // $user = Db::table("users")->where("id",2)->order("id","desc")->find();
        //$user = Db::table("users")->where("id",2)->order("id","desc")->select();
        //dump($user);

        $userQuery = Db::table("users");
        //$data = $userQuery->where("id",1)->find();
        $userQuery->order("id","desc")->select();
        $userQuery->select();//还是一样保留上次的order by
        $userQuery->removeOption('order')->select();
        return json(Db::getLastSql());
    }

    function demo(){
        $data = Db::connect("tp2")->table("users")->select();
        return json($data);
    }

    function getUser(){

        $data = Users::select();
        return json($data);
    }

    function insert(){


        $data = [
            "id"=>1,
            "name"=>"小毛8",
            "age"=>19,
            "sex"=>1,
            //"city"=>"北京 "
        ];

        //return Db::table("users")->insert($data);
        //return Db::table("users")->strict(false)->insert($data);
        //Db::table("users")->replace()->insert($data);
        //return json(Db::getLastSql());

        //return Db::table("users")->insertGetId($data);
        //return Db::table("users")->save($data);
        return Db::table("users")->field("id,name,sex")->insert($data);
    }

    function insertAll(){

        $dataAll = [
            [
                "name"=>"大白",
                "age"=>19,
                "sex"=>1,
                //"city"=>"北京 "
            ],
            [
                "name"=>"宋江",
                "age"=>33,
                "sex"=>0,
                //"city"=>"北京 "
            ]

        ];

        return Db::table("users")->insertAll($dataAll);
    }

    function update(){

//        $data = [
//            "name"=>"玉皇大帝"
//        ];
//
//        return Db::table("users")->where("id",1)->update($data);

//        $data = [
//            "id"=>1,
//            "name"=>"如来佛主"
//        ];
//
//        //return Db::table("users")->update($data);
//        return Db::table("users")->where("id",1)
//            ->exp("name","UPPER(name)")//功能只是把该字段的值变成大写
//            ->update();//啥都不修改

        //return Db::table("users")->where("id",1)->inc("sex",2)->dec("age")->update();
//        return Db::table("users")->where("id",2)->update([
//            "age"=>Db::raw("age+2"),
//            "sex"=>Db::raw("sex*10")
//        ]);

        //return Db::table("users")->where("id",1)->save(['name'=>"美国总统"]);
        //return Db::table("users")->delete("1");
        //return Db::table("users")->delete([15,16]);
        return Db::table("users")->where("id",17)->delete();

    }

    function query(){

        //$data = Db::table("users")->where("id","=",2)->find();
        //$data = Db::table("users")->where("id","<>",2)->find();
        //$data = Db::table("users")->where("id","<>",2)->select();
        //$data = Db::table("users")->where("id",">",2)->select();
       // $data = Db::table("users")->where("name","like","小%")->select();
        //$data = Db::table("users")->where("name","like",["小%","上%"],"or")->select();
        //$data = Db::table("users")->whereLike("name","小%")->select();
       // $data = Db::table("users")->whereNotLike("name","小%")->select();
        //$data = Db::table("users")->where("id","between",[4,6])->select();
        //$data = Db::table("users")->where("id","in",[4,5,6,7])->select();
       // $data = Db::table("users")->where("id","not in",[4,5,6,7])->select();
        //$data = Db::table("users")->whereNotIn("id",[4,5,6,7,8,9,10])->select();
        //$data = Db::table("users")->whereBetween("id","4,10")->select();
        //$data = Db::table("users")->whereNull("id")->select();
        //$data = Db::table("users")->where("id","not null")->select();
        $data = Db::table("users")->where("id","exp","IN(4,5,6)")->select();
        echo Db::getLastSql();
        return json($data);
    }

    function time(){

        //$data = Db::table("users")->where("create_time",">","2021-10-20")->select();
        //$data = Db::table("users")->where("create_time","between",["2021-09-01","2021-10-05"])->select();
        //$data = Db::table("users")->whereTime("create_time","between",["2021-09-01","2021-10-05"])->select();
        //$data = Db::table("users")->whereTime("create_time",">","2021-10-20")->select();
        //$data = Db::table("users")->whereBetween("create_time",["2021-09-01","2021-10-05"])->select();
        //$data = Db::table("users")->whereYear("create_time")->select();
        //$data = Db::table("users")->whereYear("create_time","last year")->select();
        //$data = Db::table("users")->whereYear("create_time","2020")->select();
       // $data = Db::table("users")->whereMonth("create_time")->select();
        //$data = Db::table("users")->whereMonth("create_time","last month")->select();
        //$data = Db::table("users")->whereDay("create_time")->select();
        //$data = Db::table("users")->whereDay("create_time","2021-10-21")->select();
        //$data = Db::table("users")->whereMonth("create_time","2021-09")->select();
        //$data = Db::table("users")->whereTime("create_time","-2 hours")->select();
        $data = Db::table("users")->whereBetweenTimeField("create_time","create_time")->select();
        echo "\r\n".Db::getLastSql()."\r\n";
        return json($data);
    }

    function join(){

        //$ret = Db::table("users")->count();
        //$ret = Db::table("users")->count("id");
        //$ret = Db::table("users")->max("age");
        //$ret = Db::table("users")->min("age");
        //$ret = Db::table("users")->avg("age");
        //$ret = Db::table("users")->sum("age");
        //$ret = Db::table("users")->min("name",false);



        //子查询  true时可以返回sql语句
        //$ret = Db::table("users")->fetchSql(false)->select();
        //$ret = Db::table("users")->buildSql(true);
        //返回sql语句
        //$subSql = Db::table("profile")->field("uid")->where("gender","女")->buildSql(true);
        //$subSql = Db::table("profile")->field("uid")->where("gender","女")->select();
        //$ret = Db::table("users")->where("id","exp",'IN'.$subSql)->select();

        //子查询
//        $ret = Db::table("users")->where("id","in",function($query){
//
//            $query->table("profile")->field("uid")->where("gender","女");
//        })->select();
//        echo "\r\n".Db::getLastSql()."\r\n";
        //$ret = Db::query("select * from users where id>4 limit 5");
        $ret = Db::execute("insert into users values(null,'李白',20,1,'2021-09-15 19:31:51')");
        echo "\r\n".Db::getLastSql()."\r\n";
        return json($ret);
    }

    function whereUp(){

        //$ret = Db::table("users")->where("id",">",3)->select();
//        $ret = Db::table("users")->where([
//            "sex"=>1,
//            "age"=>19
//        ])->select();

//        $ret = Db::table("users")->where([
//           ["sex","=",1],
//            ["age",">",19]
//        ])->select();

        //$condtion[] = ["sex","=",0];
        //$condtion[] = ["age",">",20];
        //$ret = Db::table("users")->where($condtion)->select();
        //$ret = Db::table("users")->whereRaw("sex=0 and age>87")->select();
        //$ret = Db::table("users")->where("id=:id",["id"=>5])->select();
        //$ret = Db::table("users")->field("id,name")->select();
        //$ret = Db::table("users")->field(["name,age"])->select();
        //$ret = Db::table("users")->fieldRaw("id,name,SUM(age)")->select();
       // $ret = Db::table("users")->field(true)->select();
        //$ret = Db::table("users")->withoutField("age")->select();
        $ret = Db::table("users")->alias("a")->select();

        echo "\r\n".Db::getLastSql()."\r\n";
        return json($ret);
    }

    function linkDown(){

       // $ret = Db::table("users")->limit(6)->select();
        //$ret = Db::table("users")->limit(3,3)->order("id","desc")->select();
        //$ret = Db::table("users")->page(2,3)->order("id","asc")->select();
        //$ret = Db::table("users")->order("id","desc")->select();
       // $ret = Db::table("users")->order(["id"=>"desc","age"=>"asc"])->select();
        //$ret = Db::table("users")->orderRaw("FIELD(name,'小三') desc")->select();
        //$ret = Db::table("users")->field("sex,count(sex)")->group("sex")->select();
        //$ret = Db::table("users")->fieldRaw("sex,count(sex)")->group("sex")->select();
        $ret = Db::table("users")
            ->fieldRaw("sex,count(sex)")
            ->group("sex")
            ->having("count(sex)>3")
            ->select();
        echo "\r\n".Db::getLastSql()."\r\n";
        return json($ret);
    }

    function advance(){

//        $ret = Db::table("users")
//            ->where("name|age","like","%小%")
//            ->where("id&sex",">",10)
//            ->select();
//        $ret = Db::table("users")
//            ->where([
//                ["id",">",4],
//                ["sex","<>",0]
//            ])
//            ->select();
//        $map = [
//            ["id","exp",Db::raw(">5")],
//            ["sex","=",1]
//        ];
//        $ret = Db::name("users")
//            ->where([$map])
//            ->where("age","exp",">20")
//            ->select();
//        $map1 = [
//            ["name","like","%小%"],
//            ["age","like","%3%"]
//        ];
//        $map2 = [
//            ["name","like","%三%"],
//            ["age","like","%2%"]
//        ];
//        $ret = Db::name("users")
//            ->whereOr([$map1,$map2])
//            ->select();
//        $ret = Db::name("users")
//            ->where(function($query){
//
//                $query->where("id",">",4);
//            })
//            ->whereOr(function($query){
//                $query->where("name","like","%三%");
//            })
//            ->select();
//        $ret = Db::table("users")
//            ->whereRaw("`id`>4 or `name` like  '%三%'   ")
//            ->select();

        //预处理绑定
        $ret = Db::table("users")
            ->whereRaw("`id`>:id or `name` like  :name   ",

                [
                    "id"=>"4",
                    "name"=>"%三%"
                ])

            ->select();
        echo "\r\n".Db::getLastSql()."\r\n";
        return json($ret);
    }

    function quickly(){


//        $ret = Db::name("users")
//            ->whereColumn("id",">","sex")
//            ->select();
//        $ret = Db::name("users")
//            ->whereName("小三")
//            ->select();
//        $ret = Db::name("users")
//            ->getByName("小三")
//            ;
//        $ret = Db::name("users")
//            ->getFieldByName("小三","sex");
        $ret = Db::name("users")
            ->when(false,function($query){

                $query->where("id",">",4);
            },function($query){

                $query->where("sex",">",0);
            })
            ->select();
        echo "\r\n".Db::getLastSql()."\r\n";
        return json($ret);
    }

    function getter(){


        //$db = new DbManager();
        //$db->transaction()
//        Db::transaction(function(){
//            Db::table("users")->where("id",2)->save(["price"=>Db::raw("price+3")]);
//            Db::table("users")->where("id",4)->save(["pricess"=>Db::raw("price-3")]);
//        });

//        Db::table("users")->where("id",2)->save(["price"=>Db::raw("price+3")]);
//        Db::table("users")->where("id",4)->save(["pricess"=>Db::raw("price-3")]);
//        Db::startTrans();
//        try{
//            Db::table("users")->where("id",2)->save(["price"=>Db::raw("price+3")]);
//            Db::table("users")->where("id",4)->save(["price"=>Db::raw("price-3")]);
//            Db::commit();
//        }catch (\Exception $e){
//
//            echo "sql执行失败回滚\r\n";
//            Db::rollback();
//        }

        //获取器
        $ret = Db::name("users")->withAttr("name",function($value,$data){

            //echo "value:".$value."\r\n";
            //echo "data:".$data."\r\n";
            //print_r($data);
            return strtoupper($value)."---";

        })->select();
        return json($ret);



    }

    function collection(){

        $ret = Db::table("users")->select();


       // dump($ret);

//        echo $ret['0']['name'];
//        foreach ($ret as $obj){
//            echo $obj["sex"];
//        }
        //echo json_encode($ret);
        //dump($ret->shuffle());
        dump($ret->whereIn("id",[2,4,5,6]));
    }
}